/**   
 * 文件名：PrtTrans.java   
 *   
 * 版本信息：   
 * 日期：2013年11月15日   
 * Copyright 足下 Corporation 2013    
 * 版权所有   
 *   
 */
package serv;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Vector;

/**
 * 此类描述的是： 交易报表，数据封装,后加了发卡报表、还款报表、还款结果报表
 * 
 * @author: 朱凯锋
 * @version: 2013年11月15日 上午11:34:08
 */
public class PrtTrans {
	String hk_s = null;
	String hk_e = null;
	public GwkDB gwkdb_total = new GwkDB();
	public GwkDB gwkdb_detail = new GwkDB();
	String corp_id = null;
	String payno = null;
	String cardno = null;

	/**
	 * @param payno
	 *            the payno to set
	 */
	public void setPayno(String payno) {
		this.payno = payno;
	}

	/**
	 * @param cardno
	 *            the cardno to set
	 */
	public void setCardno(String cardno) {
		this.cardno = cardno;
	}

	/**
	 * @param corp_id
	 *            the corp_id to set
	 */
	public void setCorp_id(String corp_id) {
		this.corp_id = corp_id;
	}

	/**
	 * hk_s
	 * 
	 * @return the hk_s
	 * @since CodingExample Ver(编码范例查看) 1.0
	 */
	public String getHk_s() {
		return hk_s;
	}

	/**
	 * hk_e
	 * 
	 * @return the hk_e
	 * @since CodingExample Ver(编码范例查看) 1.0
	 */
	public String getHk_e() {
		return hk_e;
	}

	/**
	 * @param hk_s
	 *            the hk_s to set
	 */
	public void setHk_s(String hk_s) {
		this.hk_s = hk_s;
	}

	/**
	 * @param hk_e
	 *            the hk_e to set
	 */
	public void setHk_e(String hk_e) {
		this.hk_e = hk_e;
	}

	/**
	 * 此方法描述的是： 按机构明细
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月15日 下午12:21:31
	 */
	public int OrganListdetail(int id) {
		// TODO Auto-generated method stub
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name,bb.cnt_bs, bb.sum_amount  ";
		sql += "from ( select a.ban_bank_no,aa.organ_level_a,a.unit_no,a.unit_name,aa.cnt_bs, aa.sum_amount  ";
		sql += "from gx_corp_bank_unit a, ( select c.organ_level_a,count(b.card_no) cnt_bs, to_char(sum(b.trans_amount),'9999999999.99') sum_amount  ";
		sql += "from gx_corp_cus c, gx_corp_trans b  ";
		sql += "where c.card_no(+) = b.card_no  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "group by c.organ_level_a ) aa  ";
		sql += "where  a.organ_level_a(+) = aa.organ_level_a  ";
		sql += "  and  a.ban_bank_no = '" + gwkdb_total.dbvt.get(id).get(1)
				+ "'  ";
		sql += "  ) bb,  gx_corp_bank_organ cc  ";
		// sql += "where bb.ban_bank_no = cc.bank_organ_id(+)  ";
		sql += "where bb.ban_bank_no = cc.bank_organ_id  ";
		sql += " order by  bb.ban_bank_no ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 6);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ret;
	}

	public int OrganListtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,sum(bb.cnt_bs), sum(bb.sum_amount)  ";
		sql += "from ( select a.ban_bank_no,aa.organ_level_a,a.unit_no,a.unit_name,aa.cnt_bs, aa.sum_amount  ";
		sql += "from gx_corp_bank_unit a, ( select c.organ_level_a,count(b.card_no) cnt_bs, sum(b.trans_amount) sum_amount  ";
		sql += "from gx_corp_cus c, gx_corp_trans b  ";
		sql += "where c.card_no(+) = b.card_no  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "group by c.organ_level_a ) aa  ";
		sql += "where  a.organ_level_a(+) = aa.organ_level_a ";
		sql += "and a.ban_bank_no in (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += ",";
			sql += "'" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "  ) bb,  gx_corp_bank_organ cc  ";
		sql += "where bb.ban_bank_no = cc.bank_organ_id  ";
		sql += " group by cc.bank_organ_name,bb.ban_bank_no  ";
		sql += " order by  bb.ban_bank_no ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	/**
	 * 此方法描述的是： 按机构汇总
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 上午10:20:48
	 */
	public int OrganHZtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select  sum(bb.cnt_bs),   sum(bb.sum_amount) ";
		sql += " from ( select a.ban_bank_no,sum(aa.cnt_bs) cnt_bs, sum(aa.sum_amount) sum_amount ";
		sql += "from gx_corp_bank_unit a, ( select c.organ_level_a,count(b.card_no) cnt_bs, sum(b.trans_amount) sum_amount ";
		sql += " from gx_corp_cus c, gx_corp_trans b ";
		sql += " where c.card_no(+) = b.card_no ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "group by c.organ_level_a ) aa ";
		sql += "where a.organ_level_a(+) = aa.organ_level_a  ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "and  length(a.unit_no) > 0 ";
		sql += "group by a.ban_bank_no) bb,  gx_corp_bank_organ  cc ";
		sql += "where bb.ban_bank_no = cc.bank_organ_id(+) ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("xxx size:" + gwkdb_total.dbvt.size());
		if (ret == 0) {
			System.out.println("size:" + gwkdb_total.dbvt.size());
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int OrganHZdetail(Vector<Vector<String>> vt_organ) {
		String sql = "select  bb.ban_bank_no, cc.bank_organ_name,  bb.cnt_bs,   bb.sum_amount ";
		sql += "from ( select a.ban_bank_no,sum(aa.cnt_bs) cnt_bs, sum(aa.sum_amount) sum_amount ";
		sql += "from gx_corp_bank_unit a, ( select c.organ_level_a,count(b.card_no) cnt_bs, to_char(sum(b.trans_amount),'9999999999.99') sum_amount ";
		sql += "from gx_corp_cus c, gx_corp_trans b ";
		sql += "where c.card_no(+) = b.card_no ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "group by c.organ_level_a ) aa ";
		sql += " where a.organ_level_a(+) = aa.organ_level_a  ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "and  length(a.unit_no) > 0 ";
		sql += "group by a.ban_bank_no) bb,  gx_corp_bank_organ  cc ";
		sql += "where bb.ban_bank_no = cc.bank_organ_id(+) ";
		sql += "order by  bb.ban_bank_no  ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ret;
	}

	/**
	 * 此方法描述的是： 按单位明细
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 上午11:30:13
	 */
	public int DwListtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select   a.unit_no, a.unit_name,a.organ_level_a, sum(aa.cnt_bs),sum(aa.sum_amount) ";
		sql += "from gx_corp_bank_unit a, ";
		sql += "( select c.organ_level_a,  count(b.card_no) cnt_bs,  to_char(sum(b.trans_amount),'9999999999.99') sum_amount ";
		sql += "from gx_corp_cus c, gx_corp_trans b ";
		sql += "where c.card_no(+) = b.card_no ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "group by c.organ_level_a ) aa ";
		sql += "where a.organ_level_a(+) = aa.organ_level_a ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += " and  length(a.unit_no) > 0 ";
		sql += "group by a.organ_level_a,a.unit_no,a.unit_name ";
		sql += "order by a.organ_level_a,a.unit_no,a.unit_name ";
		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 5);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	// 优化条件，去掉ban_bank_no
	public int DwListdetail(int id) {
		String sql = "select aa.card_no,  aa.cardholder_name,  aa.cnt_bs,  aa.sum_amount ";
		sql += "from gx_corp_bank_unit a,  ";
		sql += "( select c.organ_level_a,  b.card_no,  c.cardholder_name,  count(b.card_no)  cnt_bs,  to_char(sum(b.trans_amount),'9999999999.99')  sum_amount ";
		sql += "from gx_corp_cus c, gx_corp_trans b ";
		sql += "where c.card_no(+) = b.card_no ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "group by c.organ_level_a,b.card_no,c.cardholder_name )  aa  ";
		sql += "where a.organ_level_a(+) = aa.organ_level_a ";
		sql += "and a.organ_level_a = '" + gwkdb_total.dbvt.get(id).get(2)
				+ "'  ";
		sql += "and a.unit_no = '" + gwkdb_total.dbvt.get(id).get(0) + "'  ";
		sql += "order by aa.card_no,  aa.cardholder_name ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ret;

	}

	public int DwListtotal2(Vector<Vector<String>> vt_organ) {
		String sql = "select  sum(aa.cnt_bs),      sum(aa.sum_amount) ";
		sql += "from gx_corp_bank_unit a, ";
		sql += "( select c.organ_level_a,  count(b.card_no) cnt_bs,  sum(b.trans_amount) sum_amount ";
		sql += "from gx_corp_cus c, gx_corp_trans b ";
		sql += "where c.card_no(+) = b.card_no ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "group by c.organ_level_a ) aa ";
		sql += "where a.organ_level_a(+) = aa.organ_level_a ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ret;

	}

	/**
	 * 此方法描述的是： 按单位合计
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 上午11:30:13
	 */
	public int DwHZtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,sum(bb.cnt_bs), sum(bb.sum_amount)  ";
		sql += "from ( select a.ban_bank_no,aa.organ_level_a,a.unit_no,a.unit_name,aa.cnt_bs, aa.sum_amount  ";
		sql += "from gx_corp_bank_unit a, ( select c.organ_level_a,count(b.card_no) cnt_bs, sum(b.trans_amount) sum_amount  ";
		sql += "from gx_corp_cus c, gx_corp_trans b  ";
		sql += "where c.card_no(+) = b.card_no  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "'  ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "  group by c.organ_level_a ) aa  ";
		sql += "where  a.organ_level_a(+) = aa.organ_level_a  ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";

		sql += "  ) bb,  gx_corp_bank_organ cc  ";
		sql += "where bb.ban_bank_no = cc.bank_organ_id  ";
		sql += " group by cc.bank_organ_name,bb.ban_bank_no  ";
		sql += " order by  bb.ban_bank_no ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int DwHZdetail(int id) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name,bb.cnt_bs, bb.sum_amount  ";
		sql += "from ( select a.ban_bank_no,aa.organ_level_a,a.unit_no,a.unit_name,aa.cnt_bs, aa.sum_amount  ";
		sql += "from gx_corp_bank_unit a, ( select c.organ_level_a,count(b.card_no) cnt_bs, to_char(sum(b.trans_amount),'9999999999.99') sum_amount  ";
		sql += "from gx_corp_cus c, gx_corp_trans b  ";
		sql += "where c.card_no(+) = b.card_no  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "'  ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "  group by c.organ_level_a ) aa  ";
		sql += "where  a.organ_level_a(+) = aa.organ_level_a  ";
		sql += "and  a.ban_bank_no = '" + gwkdb_total.dbvt.get(id).get(1)
				+ "'  ";
		sql += "  ) bb,  gx_corp_bank_organ cc  ";
		sql += "where bb.ban_bank_no = cc.bank_organ_id  ";
		// sql += " order by  bb.ban_bank_no ";
		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 6);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	public int DwHZtotal2(Vector<Vector<String>> vt_organ) {
		String sql = "select sum(bb.cnt_bs), sum(bb.sum_amount)  ";
		sql += "from ( select a.ban_bank_no,aa.organ_level_a,a.unit_no,a.unit_name,aa.cnt_bs, aa.sum_amount  ";
		sql += "from gx_corp_bank_unit a, ( select c.organ_level_a,count(b.card_no) cnt_bs, sum(b.trans_amount) sum_amount  ";
		sql += "from gx_corp_cus c, gx_corp_trans b  ";
		sql += "where c.card_no(+) = b.card_no  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= '" + hk_s + "'  ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= '" + hk_e + "' ";
		sql += "  group by c.organ_level_a ) aa  ";
		sql += "where  a.organ_level_a(+) = aa.organ_level_a  ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "  ) bb,  gx_corp_bank_organ cc  ";
		sql += "where bb.ban_bank_no = cc.bank_organ_id  ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * 此方法描述的是： 按机构发卡报表
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 下午6:26:55
	 */
	public int tuckListtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,sum(nvl(aa.cntx,0)),sum(bb.cntz) ";
		sql += "from ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntx  ";
		sql += "from gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.issue_card_date >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.issue_card_date <= '" + hk_e + "' ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name ";
		sql += ") aa,  ";
		sql += "( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntz  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a   ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += " group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name ";
		sql += ") bb,  ";
		sql += "gx_corp_bank_organ cc  ";
		sql += "where aa.organ_level_a(+) = bb.organ_level_a  ";
		// sql += " and bb.ban_bank_no = cc.bank_organ_id(+)  ";
		sql += "and bb.ban_bank_no = cc.bank_organ_id  ";
		sql += "group by cc.bank_organ_name, bb.ban_bank_no  ";
		sql += "order by cc.bank_organ_name, bb.ban_bank_no  ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;

	}

	public int tuckListdetail(int id) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name,sum(nvl(aa.cntx,0)),sum(bb.cntz) ";
		sql += "from ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntx  ";
		sql += "         from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "        where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.issue_card_date >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.issue_card_date <= '" + hk_e + "' ";
		sql += "and  a.ban_bank_no = '" + gwkdb_total.dbvt.get(id).get(1)
				+ "'  ";
		sql += "        group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name   ";
		sql += "     ) aa,  ";
		sql += "     ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntz  ";
		sql += "         from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "        where  a.organ_level_a(+) = b.organ_level_a   ";
		sql += " and  a.ban_bank_no = '" + gwkdb_total.dbvt.get(id).get(1)
				+ "'  ";
		sql += " group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name  ";
		sql += " ) bb,  ";
		sql += "gx_corp_bank_organ cc  ";
		sql += "where aa.organ_level_a(+) = bb.organ_level_a  ";
		// sql += " and bb.ban_bank_no = cc.bank_organ_id(+)  ";
		sql += "and bb.ban_bank_no = cc.bank_organ_id  ";
		sql += "group by cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name ";
		sql += "order by cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 6);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ret;

	}

	/**
	 * 此方法描述的是： 按机构合计发卡
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 上午11:30:13
	 */
	public int tuckHZtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,sum(nvl(aa.cntx,0)),sum(bb.cntz) ";
		sql += "from ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntx  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.issue_card_date >='" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.issue_card_date <='" + hk_e + "' ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name   ";
		sql += ") aa,  ";
		sql += "( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntz  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a   ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name  ";
		sql += ") bb,  ";
		sql += "gx_corp_bank_organ cc  ";
		sql += "where aa.organ_level_a(+) = bb.organ_level_a  ";
		// sql += " and bb.ban_bank_no = cc.bank_organ_id(+)  ";
		sql += "and bb.ban_bank_no = cc.bank_organ_id  ";
		sql += " group by cc.bank_organ_name, bb.ban_bank_no  ";
		sql += " order by cc.bank_organ_name, bb.ban_bank_no  ";
		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int tuckHZtotal2(Vector<Vector<String>> vt_organ) {
		String sql = "select sum(nvl(aa.cntx,0)),sum(bb.cntz) ";
		sql += "from ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntx  ";
		sql += "         from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "        where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.issue_card_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.issue_card_date  <='" + hk_e + "' ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "        group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name   ";
		sql += "     ) aa,  ";
		sql += "     ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntz  ";
		sql += "         from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "        where  a.organ_level_a(+) = b.organ_level_a   ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name  ";
		sql += ") bb,  ";
		sql += "gx_corp_bank_organ cc  ";
		sql += "where aa.organ_level_a(+) = bb.organ_level_a  ";
		// sql += " and bb.ban_bank_no = cc.bank_organ_id(+)  ";
		sql += "and bb.ban_bank_no = cc.bank_organ_id  ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * 此方法描述的是： 按机构发卡报表
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 下午6:26:55
	 */
	public int tuckDwtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select a.unit_no, a.organ_level_a,a.unit_name,count(b.card_no)  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += " and b.issue_card_date  >= '" + hk_s + "'  ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += " and b.issue_card_date  <= '" + hk_e + "' ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group by a.unit_no,a.organ_level_a, a.unit_name ";
		sql += "order by a.unit_no,a.organ_level_a, a.unit_name ";
		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int tuckDwdetail(int id) {
		String sql = "select  a.unit_name,      b.card_no,       b.cardholder_name,   b.issue_card_date   ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += " and b.issue_card_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += " and b.issue_card_date  <='" + hk_e + "' ";
		sql += "and a.organ_level_a = '" + gwkdb_total.dbvt.get(id).get(1)
				+ "'  ";
		// sql += "and a.unit_no <= :as_unit_no_end ";
		sql += "order by b.card_no,  b.cardholder_name ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	public int tuckDwtotal2(Vector<Vector<String>> vt_organ) {
		String sql = "select  count(b.card_no)     ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += " and b.issue_card_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += " and b.issue_card_date  <='" + hk_e + "' ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * 此方法描述的是： 按单位发卡报表，合计
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 下午6:26:55
	 */
	public int tuckHzDwtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,sum(nvl(aa.cntx,0)),sum(bb.cntz) ";
		sql += "from ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntx  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.issue_card_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.issue_card_date  <='" + hk_e + "' ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name   ";
		sql += ") aa,  ";
		sql += "( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntz  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a   ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name  ";
		sql += ") bb,  ";
		sql += "gx_corp_bank_organ cc  ";
		sql += "where aa.organ_level_a(+) = bb.organ_level_a  ";
		// sql += " and bb.ban_bank_no = cc.bank_organ_id(+)  ";
		sql += "and bb.ban_bank_no = cc.bank_organ_id  ";
		sql += " group by cc.bank_organ_name, bb.ban_bank_no  ";
		sql += " order by cc.bank_organ_name, bb.ban_bank_no  ";
		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 4);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int tuckHzDwdetaill(int id) {
		String sql = "select cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name,sum(nvl(aa.cntx,0)),sum(bb.cntz) ";
		sql += "from ( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntx  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a  ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.issue_card_date  >= '" + hk_s + "' ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.issue_card_date  <='" + hk_e + "'  ";
		sql += "and  a.ban_bank_no = '" + gwkdb_total.dbvt.get(id).get(1)
				+ "'  ";
		sql += "group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name   ";
		sql += " ) aa,  ";
		sql += "( select a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name,count(*) cntz  ";
		sql += "from   gx_corp_bank_unit a, gx_corp_cus b  ";
		sql += "where  a.organ_level_a(+) = b.organ_level_a   ";
		sql += " and  a.ban_bank_no = '" + gwkdb_total.dbvt.get(id).get(1)
				+ "'  ";
		sql += " group  by  a.ban_bank_no,b.organ_level_a,a.unit_no,a.unit_name  ";
		sql += " ) bb,  ";
		sql += "gx_corp_bank_organ cc  ";
		sql += "where aa.organ_level_a(+) = bb.organ_level_a  ";
		// sql += " and bb.ban_bank_no = cc.bank_organ_id(+)  ";
		sql += "and bb.ban_bank_no = cc.bank_organ_id  ";
		sql += "group by cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name ";
		sql += "order by cc.bank_organ_name,bb.ban_bank_no,bb.unit_no,bb.unit_name ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 6);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * 此方法描述的是： 按卡还款报表
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 下午6:26:55
	 */
	public int hkCardtotal(Vector<Vector<String>> vt_organ) {
		String sql = "select b.payee_sum_code,c.cardholder_name,c.issue_card_date,count(*),sum(b.TAKEBACK_MONEY) ";
		sql += "from gx_corp_bank_organ a, GX_CORP_PAY_BILL_INFO b,GX_CORP_cus c, gx_corp_bank_unit d ";
		sql += "where d.BAN_BANK_NO = A.bank_organ_id  and b.payee_sum_code = c.card_no ";
		sql += "and d.organ_level_a = c.organ_level_a ";
		if ((cardno != null) && (cardno.length() > 0)) {
			sql += " and b.payee_sum_code like '%" + cardno + "%' ";
		}
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.settle_time  >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.settle_time  < to_date('" + hk_e + "','yyyymmdd')+1 ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "d.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group by b.payee_sum_code,c.cardholder_name,c.issue_card_date ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 5);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int hkCarddetail(int id) {
		String sql = "select bb.ban_bank_no,bb.unit_no,bb.organ_level_a,bb.card_no,bb.cardholder_name,bb.issue_card_date,";
		sql += "bb.trans_date,bb.trans_amount,bb.appro_no,bb.merchant_id, bb.business_name,";
		sql += "to_char(cc.settle_time,'yyyymmdd'), cc.settle, decode(cc.settle,'000',1,0) ck_cnt ";
		sql += "from ( select a.ban_bank_no,a.unit_no,aa.organ_level_a,aa.card_no,aa.cardholder_name,aa.issue_card_date,";
		sql += "aa.trans_date ,to_char(aa.trans_amount,'fm999999990.00') trans_amount,aa.appro_no,aa.merchant_id, aa.business_name ";
		sql += "from gx_corp_bank_unit a,";
		sql += "( select c.organ_level_a,b.card_no,c.cardholder_name,c.issue_card_date, ";
		sql += "b.trans_date,b.trans_amount,b.appro_no,b.merchant_id, b.business_name ";
		sql += "from gx_corp_cus c, gx_corp_trans b ";
		sql += "where c.card_no = b.card_no ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.trans_date  >= to_char(to_date('" + hk_s
					+ "','yyyymmdd'),'yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.trans_date  <= to_char(to_date('" + hk_e
					+ "','yyyymmdd'),'yyyymmdd') ";
		sql += " and b.card_no = '" + gwkdb_total.dbvt.get(id).get(0) + "' ";

		sql += ")aa ";
		sql += " where a.organ_level_a = aa.organ_level_a  ";
		sql += ") bb, ";
		sql += "gx_corp_pay_bill_info cc ";
		sql += "where cc.payee_sum_code(+) = bb.card_no   ";
		sql += "and cc.takeback_money(+) = bb.trans_amount ";
		// sql +=
		// "and to_date(cc.file_time,'yyyymmdd') - to_date(bb.trans_date,'yyyymmdd')(+) <= 10 ";
		sql += "and cc.file_time(+) >= bb.trans_date ";
		sql += "and cc.corp_id(+) = bb.unit_no   ";
		sql += "order by bb.card_no,bb.trans_date ";
		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 14);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	public int hkCardtotal2(Vector<Vector<String>> vt_organ) {
		String sql = "select count(*),sum(b.TAKEBACK_MONEY) ";
		sql += "from gx_corp_bank_organ a, GX_CORP_PAY_BILL_INFO b,GX_CORP_cus c, gx_corp_bank_unit d ";
		sql += "where d.BAN_BANK_NO = A.bank_organ_id  and b.payee_sum_code = c.card_no ";
		sql += "and b.corp_id = d.unit_no and d.organ_level_a = c.organ_level_a ";
		if ((corp_id != null) && (corp_id.length() > 0)) {
			sql += " and b.corp_id like '%" + corp_id + "%' ";
		}
		if ((payno != null) && (payno.length() > 0)) {
			sql += " and b.PAY_BILL_INFO_ID like '%" + payno + "%' ";
		}
		if ((cardno != null) && (cardno.length() > 0)) {
			sql += " and b.PAYEE_SUM_CODE like '%" + cardno + "%' ";
		}
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.settle_time  >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.settle_time  < to_date('" + hk_e + "','yyyymmdd')+1 ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "d.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * 此方法描述的是： 按机构还款结果报表
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 下午6:26:55
	 */
	public int hkresdetail(int id) {
		String sql = "select a.PAYEE_SUM_CODE,count(a.PAYEE_SUM_CODE),to_char(sum(a.TAKEBACK_MONEY),'99999999.99') ";
		sql += "from GX_CORP_PAY_BILL_INFO a, GX_CORP_BANK_UNIT b, GX_CORP_CUS c,gx_corp_bank_organ d ";
		sql += "where b.BAN_BANK_NO = d.bank_organ_id  and a.payee_sum_code = c.card_no ";
		sql += "and b.organ_level_a = c.organ_level_a ";
		sql += "and a.settle = '000' ";

		// sql += "and a.settle = '000' ";
		if ((corp_id != null) && (corp_id.length() > 0)) {
			sql += " and b.unit_no like '%" + corp_id + "%' ";
		}
		if ((payno != null) && (payno.length() > 0)) {
			sql += " and a.PAY_BILL_INFO_ID like '%" + payno + "%' ";
		}
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and a.settle_time  >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and a.settle_time  < to_date('" + hk_e + "','yyyymmdd')+1 ";
		sql += "and d.bank_organ_id = '" + gwkdb_total.dbvt.get(id).get(0)
				+ "'";
		sql += "group by a.PAYEE_SUM_CODE ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 3);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;

	}

	/**
	 * 此方法描述的是： 按机构还款结果报表、汇总
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 下午6:26:55
	 */
	public int hkresHztotal(Vector<Vector<String>> vt_organ) {
		String sql = "select aa.ban_bank_no,cc.bank_organ_name,aa.cnt_bsz,  bb.cnt_bsx, "
				+ "aa.sum_amountz, nvl(bb.sum_amountx,0) cg_amount, aa.sum_amountz - nvl(bb.sum_amountx,0) sb_amount ";
		sql += "from (select a.ban_bank_no,count(b.payee_sum_code) cnt_bsz,  sum(b.takeback_money) sum_amountz ";
		sql += "from gx_corp_bank_unit a,gx_corp_pay_bill_info b , gx_corp_cus c ";
		sql += " where a.unit_no = b.corp_id and b.PAYEE_SUM_CODE = c.card_no and a.organ_level_a = c.organ_level_a ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.settle_time  >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.settle_time  < to_date('" + hk_e + "','yyyymmdd')+1 ";
		if ((corp_id != null) && (corp_id.length() > 0)) {
			sql += " and b.corp_id like '%" + corp_id + "%' ";
		}
		if ((payno != null) && (payno.length() > 0)) {
			sql += " and b.PAY_BILL_INFO_ID like '%" + payno + "%' ";
		}
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += " group by a.ban_bank_no ) aa, ";
		sql += " (select a.ban_bank_no, count(b.payee_sum_code) cnt_bsx,  sum(b.takeback_money) sum_amountx ";
		sql += " from gx_corp_bank_unit a,gx_corp_pay_bill_info b , gx_corp_cus c";
		sql += " where a.unit_no = b.corp_id and b.PAYEE_SUM_CODE = c.card_no and a.organ_level_a = c.organ_level_a ";
		sql += " and b.settle = '000' ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.settle_time  >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.settle_time  < to_date('" + hk_e + "','yyyymmdd')+1 ";
		if ((corp_id != null) && (corp_id.length() > 0)) {
			sql += " and b.corp_id like '%" + corp_id + "%' ";
		}
		if ((payno != null) && (payno.length() > 0)) {
			sql += " and b.PAY_BILL_INFO_ID like '%" + payno + "%' ";
		}
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "a.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";
		sql += "group by a.ban_bank_no ) bb ,gx_corp_bank_organ cc ";
		sql += "where bb.ban_bank_no = aa.ban_bank_no ";
		sql += "and bb.ban_bank_no = cc.bank_organ_id ";
		sql += "order by aa.ban_bank_no";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 7);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int hkrestotal2(Vector<Vector<String>> vt_organ) {
		String sql = "select count(*),sum(b.TAKEBACK_MONEY) ";
		sql += "from gx_corp_bank_organ a, GX_CORP_PAY_BILL_INFO b,GX_CORP_cus c, gx_corp_bank_unit d ";
		sql += "where d.BAN_BANK_NO = A.bank_organ_id  and b.payee_sum_code = c.card_no ";
		sql += "and b.corp_id = d.unit_no and d.organ_level_a = c.organ_level_a ";
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.settle_time >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.settle_time < to_date('" + hk_e
					+ "','yyyymmdd') +1  ";
		sql += "and (";
		for (int j = 0; j < vt_organ.size(); j++) {
			if (j != 0)
				sql += "or ";
			sql += "d.ban_bank_no='" + vt_organ.get(j).get(0).toString() + "' ";
		}
		sql += ") ";

		int ret = -1;
		try {
			ret = gwkdb_total.query(sql, 2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}

	/**
	 * 此方法描述的是： 按单位还款结果报表
	 * 
	 * @author: 朱凯锋
	 * @version: 2013年11月16日 下午6:26:55
	 */
	public int hkresDwtotal(Vector<Vector<String>> vt_organ, int id) {
		String sql = "select a.bank_organ_id,a.bank_organ_name,b.corp_id, d.unit_name ,count(*),sum(b.TAKEBACK_MONEY) ";
		sql += "from gx_corp_bank_organ a, GX_CORP_PAY_BILL_INFO b,GX_CORP_cus c, gx_corp_bank_unit d ";
		sql += "where d.BAN_BANK_NO = A.bank_organ_id  and b.payee_sum_code = c.card_no ";
		sql += "and b.corp_id = d.unit_no and d.organ_level_a = c.organ_level_a ";
		sql += "and b.settle = '000'";
		if ((corp_id != null) && (corp_id.length() > 0)) {
			sql += " and a.corp_id like '%" + corp_id + "%' ";
		}
		if ((payno != null) && (payno.length() > 0)) {
			sql += " and b.PAY_BILL_INFO_ID like '%" + payno + "%' ";
		}
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and b.settle_time  >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and b.settle_time  < to_date('" + hk_e + "','yyyymmdd')+1 ";
		if (vt_organ != null) {
			// 用按单位出明细
			sql += "and (";
			for (int j = 0; j < vt_organ.size(); j++) {
				if (j != 0)
					sql += "or ";
				sql += "d.ban_bank_no='" + vt_organ.get(j).get(0).toString()
						+ "' ";
			}
			sql += ") ";
		} else {
			sql += "and a.bank_organ_id='"
					+ gwkdb_total.dbvt.get(id).get(0).toString() + "' ";
		}

		sql += "group by a.bank_organ_name,b.corp_id, d.unit_no,d.unit_name,a.bank_organ_id ";
		sql += "order by a.bank_organ_id ";

		int ret = -1;
		try {
			if (vt_organ != null) {
				ret = gwkdb_total.query(sql, 6);
			} else
				ret = gwkdb_detail.query(sql, 6);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (ret == 0) {
			ret = gwkdb_total.dbvt.size();
		}
		return ret;
	}

	public int hkresDwdetail(Vector<Vector<String>> vt_organ, int id) {
		String sql = "select a.PAY_BILL_INFO_ID ,to_char(a.TAKEBACK_MONEY,'9999999.99'),a.corp_id , b.unit_name , ";
		sql += "a.PAYEE_SUM_CODE ,to_char(a.settle_time,'yyyy-mm-dd') , a.settle ";
		sql += "from GX_CORP_PAY_BILL_INFO a, GX_CORP_BANK_UNIT b, GX_CORP_CUS c ";
		sql += "where a.payee_sum_code = c.card_no ";
		sql += "and c.organ_level_a = b.organ_level_a ";

		if ((corp_id != null) && (corp_id.length() > 0)) {
			sql += " and b.unit_no like '%" + corp_id + "%' ";
		}
		if ((payno != null) && (payno.length() > 0)) {
			sql += " and a.PAY_BILL_INFO_ID like '%" + payno + "%' ";
		}
		if ((cardno != null) && (cardno.length() > 0)) {
			sql += " and a.payee_sum_code like '%" + cardno + "%' ";
		}
		// 有确认日期不判settle_time
		if ((hk_s != null) && (hk_s.length() > 0))
			sql += "and a.settle_time  >= to_date('" + hk_s + "','yyyymmdd') ";
		if ((hk_e != null) && (hk_e.length() > 0))
			sql += "and a.settle_time  < to_date('" + hk_e + "','yyyymmdd')+1 ";
		if (vt_organ != null) {
			// 用按单位出明细
			sql += "and (";
			for (int j = 0; j < vt_organ.size(); j++) {
				if (j != 0)
					sql += "or ";
				sql += "b.ban_bank_no='" + vt_organ.get(j).get(0).toString()
						+ "' ";
			}
			sql += ") ";
		} else {
			sql += "and b.ban_bank_no = '" + gwkdb_total.dbvt.get(id).get(0)
					+ "'";
		}
		sql += "order by a.corp_id, a.settle_time ";

		int ret = -1;
		try {
			ret = gwkdb_detail.query2(sql, 7);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;

	}
}
